1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Linq;
7 using System.Text;
8 using System.Windows.Forms;
9 using System.Data.SqlClient;
10 using Excel = Microsoft.Office.Interop.Excel;
11 namespace WarehouseManagementSystem
12 {
13 public partial class frmCustomersRecord : Form
14 {
15
16 SqlConnection con = null;
17 DataSet ds = new DataSet();
18 SqlCommand cmd = null;
19 DataTable dt = new DataTable();
20 ConnectionString cs = new ConnectionString();
21 public frmCustomersRecord()
22 {
23 InitializeComponent();
24 }
25 public void GetData()
26 {
27 try{
28 con = new SqlConnection(cs.DBConn);
29 con.Open();
30 cmd = new SqlCommand( "SELECT RTRIM(CustomerID)as [Customer ID],RTRIM(Customername) as [Customer Name],RTRIM(address) as [Address],RTRIM(city) as [City],RTRIM(ContactNo) as [Contact No.],RTRIM(ContactNo1) as [Contact No. 1],(email) as [Email],(notes) as [Notes] from Customer order by CustomerName", con);
31 SqlDataAdapter myDA = new SqlDataAdapter(cmd);
32 DataSet myDataSet = new DataSet();
33 myDA.Fill(myDataSet, "Customer");
34 dataGridView1.DataSource = myDataSet.Tables["Customer"].DefaultView;
35 con.Close();
36 }
37 catch (Exception ex)
38 {
39 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
40 }
41 }
42
43 private void frmCustomersRecord_Load(object sender, EventArgs e)
44 {
45 GetData();
46 }
47
48
49
50 private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
51 {
52 string strRowNumber = (e.RowIndex + 1).ToString();
53 SizeF size = e.Graphics.MeasureString(strRowNumber, this.Font);
54 if (dataGridView1.RowHeadersWidth < Convert.ToInt32((size.Width + 20)))
55 {
56 dataGridView1.RowHeadersWidth = Convert.ToInt32((size.Width + 20));
57 }
58 Brush b = SystemBrushes.ControlText;
59 e.Graphics.DrawString(strRowNumber, this.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2));
60
61 }
62
63
64
65 private void txtCustomers_TextChanged(object sender, EventArgs e)
66 {
67 try
68 {
69 con = new SqlConnection(cs.DBConn);
70 con.Open();
71 cmd = new SqlCommand("SELECT RTRIM(CustomerID)as [Customer ID],RTRIM(Customername) as [Customer Name],RTRIM(address) as [Address],RTRIM(city) as [City],RTRIM(ContactNo) as [Contact No.],RTRIM(ContactNo1) as [Contact No. 1],(email) as [Email],(notes) as [Notes] from Customer where CustomerName like '" + txtCustomers.Text + "%' order by CustomerName", con);
72 SqlDataAdapter myDA = new SqlDataAdapter(cmd);
73 DataSet myDataSet = new DataSet();
74 myDA.Fill(myDataSet, "Customer");
75 dataGridView1.DataSource = myDataSet.Tables["Customer"].DefaultView;
76
77 con.Close();
78 }
79 catch (Exception ex)
80 {
81 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
82 }
83 }
84
85 private void Button3_Click(object sender, EventArgs e)
86 {
87 if (dataGridView1.DataSource == null)
88 {
89 MessageBox.Show("Sorry nothing to export into excel sheet..", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
90 return;
91 }
92 int rowsTotal = 0;
93 int colsTotal = 0;
94 int I = 0;
95 int j = 0;
96 int iC = 0;
97 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
98 Excel.Application xlApp = new Excel.Application();
99
100 try
101 {
102 Excel.Workbook excelBook = xlApp.Workbooks.Add();
103 Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelBook.Worksheets[1];
104 xlApp.Visible = true;
105
106 rowsTotal = dataGridView1.RowCount;
107 colsTotal = dataGridView1.Columns.Count - 1;
108 var _with1 = excelWorksheet;
109 _with1.Cells.Select();
110 _with1.Cells.Delete();
111 for (iC = 0; iC <= colsTotal; iC++)
112 {
113 _with1.Cells[1, iC + 1].Value = dataGridView1.Columns[iC].HeaderText;
114 }
115 for (I = 0; I <= rowsTotal - 1; I++)
116 {
117 for (j = 0; j <= colsTotal; j++)
118 {
119 _with1.Cells[I + 2, j + 1].value = dataGridView1.Rows[I].Cells[j].Value;
120 }
121 }
122 _with1.Rows["1:1"].Font.FontStyle = "Bold";
123 _with1.Rows["1:1"].Font.Size = 12;
124
125 _with1.Cells.Columns.AutoFit();
126 _with1.Cells.Select();
127 _with1.Cells.EntireColumn.AutoFit();
128 _with1.Cells[1, 1].Select();
129 }
130 catch (Exception ex)
131 {
132 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
133 }
134 finally
135 {
136 //RELEASE ALLOACTED RESOURCES
137 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
138 xlApp = null;
139 }
140 }
141
142 private void button1_Click(object sender, EventArgs e)
143 {
144 try
145 {
146 Cursor = Cursors.WaitCursor;
147 timer1.Enabled = true;
148 rptCustomers rpt = new rptCustomers();
149 //The report you created.
150 frmCustomersReport frm = new frmCustomersReport();
151 SqlConnection myConnection = default(SqlConnection);
152 SqlCommand MyCommand = new SqlCommand();
153 SqlDataAdapter myDA = new SqlDataAdapter();
154 POS_DBDataSet myDS = new POS_DBDataSet();
155 //The DataSet you created.
156 myConnection = new SqlConnection(cs.DBConn);
157 MyCommand.Connection = myConnection;
158 MyCommand.CommandText = "select * from Customer Order by CustomerName";
159 MyCommand.CommandType = CommandType.Text;
160 myDA.SelectCommand = MyCommand;
161 myDA.Fill(myDS, "Customer");
162 rpt.SetDataSource(myDS);
163 frm.crystalReportViewer1.ReportSource = rpt;
164 frm.Show();
165 }
166 catch (Exception ex)
167 {
168 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
169 }
170 }
171
172 private void timer1_Tick(object sender, EventArgs e)
173 {
174
175 Cursor = Cursors.Default;
176 timer1.Enabled = false;
177 }
178 }
179 }
